package itsm.isperp.module.service.db;

import itsm.isperp.framework.data.commons.repository.BaseRepository;
import itsm.isperp.framework.data.dialect.DialectFactory;
import itsm.isperp.framework.service.BaseService;
import itsm.isperp.framework.web.request.JqGridFilterRuleOp;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.ResultMessage;
import itsm.isperp.module.entity.db.DbEntity;
import itsm.isperp.module.entity.db.DbEntityField;
import itsm.isperp.module.entity.db.DbEntityFieldControl;
import itsm.isperp.module.entity.db.DbEntitySlmMonitorMap;
import itsm.isperp.module.repository.db.DbEntityMapper;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jdbc.support.DatabaseType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class DbEntityService extends BaseService<DbEntity> {

	@Autowired
	protected DbEntityMapper dbEntityMapper;

	@Autowired
	protected DbEntityFieldService dbEntityFieldService;

	@Autowired
	protected SqlSessionFactory sqlSessionFactory;

	@Override
	public BaseRepository<DbEntity> getRepository() {
		return this.dbEntityMapper;
	}

	@Autowired
	protected JdbcTemplate jdbcTemplate;

	@Override
	public ResultMessage delete(String id) {

		this.dbEntityFieldService.deleteByEntityId(id);

		this.getRepository().deleteByClass(id, DbEntity.class);
		return ResultMessage.success();

	}

	/**
	 * 获取一个归类的实体模型列表
	 * 
	 * @return 归类的实体模型列表
	 */
	public Map<String, List<DbEntity>> findAllUseForImport(String categoryName) {
		JqGridRequest req = new JqGridRequest();
		req.isPageable(false);
		req.addFilterRule("use_for_import", JqGridFilterRuleOp.eq, "1");

		if (StringUtils.isNotEmpty(categoryName)) {
			req.addFilterRule("entityCategory", JqGridFilterRuleOp.eq,
					categoryName);
		}

		Map<String, List<DbEntity>> map = new HashMap<String, List<DbEntity>>();
		List<DbEntity> list = this.getRepository().findAllByRequest(req);

		for (DbEntity entity : list) {
			if (map.containsKey(entity.getEntityCategory())) {
				map.get(entity.getEntityCategory()).add(entity);
			} else {
				List<DbEntity> l = new ArrayList<DbEntity>();
				l.add(entity);
				map.put(entity.getEntityCategory(), l);
			}
		}

		return map;

	}

	/**
	 * 根据实体主见的值，获取实体指定列的Map
	 * 
	 * @param dbEndityId
	 *            实体对象
	 * @param dataId
	 *            实体数据的值
	 * @return 查询实体中定义的列的集合
	 */
	public Map<String, Object> getDataFromDbEntity(DbEntity entity,
			String dataId) throws SQLException {
		//
		// List<DbEntityField> list = dbEntityFieldService
		// .findAllByEntityId(entity.getId());
		// String cols = "";
		// for (int i = 0; i < list.size(); i++) {
		// if (i < list.size() - 1) {
		// cols = cols + list.get(i).getColumnName() + ",";
		// } else {
		// cols = cols + list.get(i).getColumnName();
		// }
		//
		// }
		//
		// if (StringUtils.isEmpty(cols)) {
		// cols = "*";
		// }
		// List<Map<String, Object>> maps = null;

		// DbEntity entity = dbEntityMapper.findOne(dbEndityId);
		String sql = "SELECT * FROM " + entity.getTableName() + " WHERE id=? ";
		return jdbcTemplate.queryForMap(sql, dataId);

	}

	public Map<String, Object> getDataFromDbEntity(String tableName,
			String dataId) throws SQLException {

		String sql = "SELECT * FROM " + tableName + " WHERE id=? ";
		return jdbcTemplate.queryForMap(sql, dataId);

	}

	/**
	 * <p>
	 * 重新生成实体的列。
	 * </p>
	 * <p>
	 * 生成规则：
	 * </p>
	 * <ul>
	 * <li>1、使用旧的实体列定义（{@link DbEntityField }）与实体最新的数据库列定义做比较（使用列的名称比较）；</li>
	 * <li>2、如果实体列定义中不存在数据库列，则新增一条实体列定义；</li>
	 * <li>3、如果列存在，比较列的类型是否一致，如果不一致跟新其为最新类型；</li>
	 * <li>4、删除原有的作废列：所谓作为列是指旧的实体列定义中包含了新实体中不存在的列以及其定義的控件；</li>
	 * </ul>
	 * 
	 * @param id
	 *            实体定义的id
	 * 
	 * @return 结果消息
	 */
	public ResultMessage generateDbEntityField(String id) {

		// 根据实体ID获取其定义的列定义
		List<DbEntityField> oldList = dbEntityFieldService
				.findAllByEntityId(id);

		// 根据实体对应的表名获取其数据库表结构
		List<Map<String, Object>> maps = this.showColumns(id, null);

		// 逐行对从表结构进行扫描，若数据列不存在于实体列定义中曾添加，若有但数据类型不一致则修改数据类型号
		for (int i = 0; i < maps.size(); i++) {
			Map<String, Object> map = maps.get(i);
			// 判断是否存在于实体列定义中，不存在则添加
			int intReturn = isExists(oldList, map);
			if (intReturn == 0) {
				addDbEntityField(id, map);
			} else if (intReturn == 2) // 更新数据类型
			{
				updateDataType(oldList, map);
			}
		}

		return ResultMessage.success();

	}

	/**
	 * 根据表结构列的定义添加实体列
	 * 
	 * @param entityId
	 * @param map
	 */
	private void addDbEntityField(String entityId, Map<String, Object> map) {
		DbEntityField newField = new DbEntityField();
		newField.setColumnName(map.get("column_name").toString());
		newField.setDbEntityId(entityId);
		newField.setFieldType(map.get("column_type").toString());
		newField.setName(map.get("column_name").toString());
		newField.setNotnull(false);
		dbEntityFieldService.saveOrUpdate(newField);

	}

	/**
	 * 根据实体列的数据类型
	 * 
	 * @param list
	 * @param map
	 */
	private void updateDataType(List<DbEntityField> list,
			Map<String, Object> map) {
		String columnName = map.get("column_name").toString();
		String type = map.get("column_type").toString();
		for (DbEntityField field : list) {
			if (field.getColumnName().equalsIgnoreCase(columnName)) {
				field.setFieldType(type);
				dbEntityFieldService.saveOrUpdate(field);
			}
		}
	}

	/**
	 * 判断表结构列是否存在于实体列定义中
	 * 
	 * @param list
	 *            实体列集合
	 * @param columnName
	 *            列
	 * @return 0:表示不存在，1：表示存在且数据类型一致；2：表示存在但数据类型不一致
	 */
	private int isExists(List<DbEntityField> list, Map<String, Object> map) {
		int intReturn = 0;
		String columnName = map.get("column_name").toString();
		String type = map.get("column_type").toString();
		for (DbEntityField field : list) {
			if (field.getColumnName().equalsIgnoreCase(columnName)) {
				intReturn = 1;
				if (!type.equalsIgnoreCase(field.getFieldType())) {
					intReturn = 2;
				}
				break;
			}
		}

		return intReturn;
	}

	/**
	 * 显示所有的表描述
	 * 
	 * @return 所有的表描述
	 */
	public List<Map<String, Object>> showTables(JqGridRequest request) {
		String filter = request == null ? null : request.getSql();

		String sql = null;
		if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) {
			String dbName = jdbcTemplate.queryForObject("SELECT database()",
					String.class);
			sql = "SELECT table_name,table_rows,create_time,update_time,table_comment FROM information_schema.tables  WHERE table_type = 'BASE TABLE' AND table_schema='"
					+ dbName
					+ "' "
					+ (filter == null ? " " : filter)
					+ " ORDER BY table_name ASC";
		} else {
			sql = "SELECT * FROM (SELECT table_name,0 AS table_rows,'' AS create_time,'' AS update_time,TABLESPACE_NAME AS table_comment  FROM USER_TABLES) table_t WHERE 1=1 "
					+ (filter == null ? " " : filter)
					+ " ORDER BY table_name ASC";
		}

		return jdbcTemplate.queryForList(sql);
	}

	/**
	 * 显示表的列描述
	 * 
	 * @param id
	 *            数据实体Id
	 * @return 表的列描述
	 */
	public List<Map<String, Object>> showColumns(String id,
			JqGridRequest request) {
		DbEntity entity = this.getRepository().findOne(id);
		return showColumnsByTableName(entity.getTableName(), request);
	}

	public List<Map<String, Object>> showColumnsByTableName(String tableName,
			JqGridRequest request) {
		String filter = request == null ? null : request.getSql();

		String sql = null;
		if (DialectFactory.getDatabaseType() == DatabaseType.MYSQL) {
			String dbName = jdbcTemplate.queryForObject("SELECT database()",
					String.class);

			sql = "SELECT column_name,column_type,column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='"
					+ dbName
					+ "' AND  TABLE_NAME='"
					+ tableName
					+ "' "
					+ (filter == null ? " " : filter)
					+ " ORDER BY column_name ASC";
		} else {
			sql = "SELECT * FROM (select  t.column_name,c.DATA_TYPE AS column_type,t.comments AS column_comment  FROM USER_COL_COMMENTS t,USER_TAB_COLUMNS c  WHERE c.column_name= t.column_name and c.TABLE_NAME='"
					+ tableName.toUpperCase()
					+ "' AND c.TABLE_NAME=t.TABLE_NAME )table_t WHERE 1=1"
					+ (filter == null ? " " : filter)
					+ " ORDER BY column_name ASC";
		}

		return jdbcTemplate.queryForList(sql);

	}

	public List<Object> findAllType() {
		return jdbcTemplate.queryForList(
				"SELECT DISTINCT entity_category FROM db_entity", Object.class);
	}

	public List<Map<String, Object>> getSlmMonitorMapList(String tableName,
			String statusName) {

		// 1.根据表名称及状态字段获取到所属的所有状态
		// 2.使用leftjoin方式得出数据
		DbEntityFieldControl fc = this.dbEntityFieldService.findFieldControl(
				tableName, statusName);
		if (fc != null
				&& DbEntityFieldControl.CONTROL_TYPE_SELECT.equals(fc
						.getControlType())) {
			String sql = this.dbEntityFieldService
					.getFieldControlDatasourceSql(fc);

			sql = "SELECT '" + tableName + "' AS table_name,t."
					+ fc.getValueField() + " AS status,b.column_name FROM ("
					+ sql + ") t LEFT JOIN db_entity_slm_monitor_map b ON t."
					+ fc.getValueField() + "=b.status";
			return this.jdbcTemplate.queryForList(sql);
		}

		return null;
	}

	public void saveMonitorMap(String tableName, String slmColumnName,
			String slmRelyTableName, List<DbEntitySlmMonitorMap> list) {
		String sql = "DELETE FROM db_entity_slm_monitor_map WHERE table_name='"
				+ tableName + "'";
		this.jdbcTemplate.update(sql);

		if (StringUtils.isEmpty(slmColumnName)) {

			sql = "UPDATE  db_entity SET use_for_slm='0',slm_monitor_column=NULL  WHERE table_name='"
					+ tableName + "'";
			this.jdbcTemplate.update(sql);
			return;
		} else {
			sql = "UPDATE  db_entity SET use_for_slm='1',slm_monitor_column='"
					+ slmColumnName + "', slm_rely_table_name='"
					+ slmRelyTableName + "'	  WHERE  table_name='" + tableName
					+ "'";
			this.jdbcTemplate.update(sql);
		}

		for (DbEntitySlmMonitorMap map : list) {
			if (StringUtils.isNotEmpty(map.getColumnName())) {
				sql = "INSERT INTO db_entity_slm_monitor_map(table_name,status,column_name) VALUES('"
						+ tableName
						+ "','"
						+ map.getStatus()
						+ "','"
						+ map.getColumnName() + "')";

				this.jdbcTemplate.update(sql);
			}
		}

	}
}